package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import bean.DongVatChanNuoiModel;
import bean.DongVatHeSinhThai;
import bean.DongVatLamSanNgoaiGoModel;
import bean.DongVatSanHoModel;
import bean.DongVatTenLaTinModel;
import bean.DongVatThamCoBienModel;

public class DongVatProcessDAO {
	private ConnectDatabaseDAO connectDatabaseDAO = new ConnectDatabaseDAO();
	private Connection con;
	private Statement st;
	
	// close connect
	private void closeConnect() throws SQLException {
		con.close();
		st.close();
		System.out.println("Closed connect");
	}
	
	//---------------------------------DONG VAT------------------------------------------
	public ArrayList<DongVatHeSinhThai> listInfoDongVatHeSinhThai() throws SQLException{
		con = connectDatabaseDAO.connect();
		ArrayList<DongVatHeSinhThai> listDongVatHeSinhThai = new ArrayList<DongVatHeSinhThai>();
		try {
			st = con.createStatement();
			String sql = "SELECT * from hstdongvat";
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				DongVatHeSinhThai dongVatHeSinhThai = new DongVatHeSinhThai();				
				dongVatHeSinhThai.setIdHSTDongVat(rs.getString("idhstdongvat"));
				dongVatHeSinhThai.setNganh(rs.getString("nganh"));
				dongVatHeSinhThai.setLop(rs.getString("lop"));
				dongVatHeSinhThai.setBo(rs.getString("bo"));
				dongVatHeSinhThai.setHo(rs.getString("ho"));
				dongVatHeSinhThai.setChi(rs.getString("chi"));
				dongVatHeSinhThai.setLoai(rs.getString("loai"));
				dongVatHeSinhThai.setNoiSong(rs.getString("noisong"));
				dongVatHeSinhThai.setMoTa(rs.getString("mota"));
				dongVatHeSinhThai.setThucAn(rs.getString("thucan"));
				dongVatHeSinhThai.setHoatDong(rs.getString("hoatdong"));
				dongVatHeSinhThai.setImage(rs.getString("image"));
				dongVatHeSinhThai.setIdDongVatTenLaTin(rs.getString("iddongvat_tenlatin"));
				
				listDongVatHeSinhThai.add(dongVatHeSinhThai);
			}
		} catch (Exception e) {
			System.out.println("Can't database excute (SELECT * from hstdongvat)");
		} finally{
			closeConnect();
		}
		return listDongVatHeSinhThai;
	}
	
	public ArrayList<DongVatChanNuoiModel> listInfoDongVatChanNuoiModel() throws SQLException{
		con = connectDatabaseDAO.connect();
		ArrayList<DongVatChanNuoiModel> listDongVatChanNuoiModel = new ArrayList<DongVatChanNuoiModel>();
		try {
			st = con.createStatement();
			String sql = "SELECT * from dongvat_channuoi";
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				DongVatChanNuoiModel dongVatChanNuoiModel = new DongVatChanNuoiModel();				
				dongVatChanNuoiModel.setIdDongVatChanNuoi(rs.getString("iddongvat_channuoi"));
				dongVatChanNuoiModel.setIdHSTDongVat(rs.getString("idhstdongvat"));
				dongVatChanNuoiModel.setNamKhaoSat(rs.getString("namkhaosat"));
				dongVatChanNuoiModel.setDoTuoi(rs.getString("dotuoi"));
				dongVatChanNuoiModel.setHinhThucSinhSan(rs.getString("hinhthucsinhsan"));
				
				listDongVatChanNuoiModel.add(dongVatChanNuoiModel);
			}
		} catch (Exception e) {
			System.out.println("Can't database excute");
		} finally{
			closeConnect();
		}
		return listDongVatChanNuoiModel;
	}
	
	public ArrayList<DongVatLamSanNgoaiGoModel> listInfoDongVatLamSanNgoaiGoModel() throws SQLException{
		con = connectDatabaseDAO.connect();
		ArrayList<DongVatLamSanNgoaiGoModel> listDongVatLamSanNgoaiGoModel = new ArrayList<DongVatLamSanNgoaiGoModel>();
		try {
			st = con.createStatement();
			String sql = "SELECT * from dongvat_lamsanngoaigo";
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				DongVatLamSanNgoaiGoModel dongVatLamSanNgoaiGoModel = new DongVatLamSanNgoaiGoModel();				
				dongVatLamSanNgoaiGoModel.setIdDVLamSanNgoaiGo(rs.getString("iddongvat_lamsanngoaigo"));
				dongVatLamSanNgoaiGoModel.setIdHSTDongVat(rs.getString("idhstdongvat"));
				dongVatLamSanNgoaiGoModel.setNamKhaoSat(rs.getString("namkhaosat"));
				dongVatLamSanNgoaiGoModel.setDoTuoi(rs.getString("dotuoi"));
				dongVatLamSanNgoaiGoModel.setHinhThucSinhSan(rs.getString("hinhthucsinhsan"));
				
				listDongVatLamSanNgoaiGoModel.add(dongVatLamSanNgoaiGoModel);
			}
		} catch (Exception e) {
			System.out.println("Can't database excute");
		} finally{
			closeConnect();
		}
		return listDongVatLamSanNgoaiGoModel;
	}
	
	public ArrayList<DongVatSanHoModel> listInfoDongVatSanHoModel() throws SQLException{
		con = connectDatabaseDAO.connect();
		ArrayList<DongVatSanHoModel> listDongVatSanHoModel = new ArrayList<DongVatSanHoModel>();
		try {
			st = con.createStatement();
			String sql = "SELECT * from dongvat_sanho";
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				DongVatSanHoModel dongVatSanHoModel = new DongVatSanHoModel();				
				dongVatSanHoModel.setIdDongVatSanHo(rs.getString("iddongvat_sanho"));
				dongVatSanHoModel.setIdHSTDongVat(rs.getString("idhstdongvat"));
				dongVatSanHoModel.setNamKhaoSat(rs.getString("namkhaosat"));
				dongVatSanHoModel.setDoTuoi(rs.getString("dotuoi"));
				dongVatSanHoModel.setHinhThucSinhSan(rs.getString("hinhthucsinhsan"));
				
				listDongVatSanHoModel.add(dongVatSanHoModel);
			}
		} catch (Exception e) {
			System.out.println("Can't database excute");
		} finally{
			closeConnect();
		}
		return listDongVatSanHoModel;
	}
	
	public ArrayList<DongVatTenLaTinModel> listInfoDongVatTenLaTinModel() throws SQLException{
		con = connectDatabaseDAO.connect();
		ArrayList<DongVatTenLaTinModel> listDongVatTenLaTinModel = new ArrayList<DongVatTenLaTinModel>();
		try {
			st = con.createStatement();
			String sql = "SELECT * from dongvat_tenlatin";
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				DongVatTenLaTinModel dongVatTenLaTinModel = new DongVatTenLaTinModel();
				dongVatTenLaTinModel.setIdDongVatTenLaTin(rs.getString("iddongvat_tenlatin"));
				dongVatTenLaTinModel.setNganhLaTin(rs.getString("nganhlatin"));
				dongVatTenLaTinModel.setLopLaTin(rs.getString("loplatin"));
				dongVatTenLaTinModel.setBoLaTin(rs.getString("bolatin"));
				dongVatTenLaTinModel.setHoLaTin(rs.getString("holatin"));
				dongVatTenLaTinModel.setChiLaTin(rs.getString("chilatin"));
				dongVatTenLaTinModel.setLoaiLaTin(rs.getString("loailatin"));
				
				listDongVatTenLaTinModel.add(dongVatTenLaTinModel);
			}
		} catch (Exception e) {
			System.out.println("Can't database excute");
		} finally{
			closeConnect();
		}
		return listDongVatTenLaTinModel;
	}
	
	public ArrayList<DongVatThamCoBienModel> listInfoDongVatThamCoBienModel() throws SQLException{
		con = connectDatabaseDAO.connect();
		ArrayList<DongVatThamCoBienModel> listDongVatThamCoBienModel = new ArrayList<DongVatThamCoBienModel>();
		try {
			st = con.createStatement();
			String sql = "SELECT * from dongvat_thamcobien";
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				DongVatThamCoBienModel dongVatThamCoBienModel = new DongVatThamCoBienModel();
				dongVatThamCoBienModel.setIdDongVatThamCoBien(rs.getString("iddongvat_thamcobien"));
				dongVatThamCoBienModel.setIdHSTDongVat(rs.getString("idhstdongvat"));
				dongVatThamCoBienModel.setNamKhaoSat(rs.getString("namkhaosat"));
				dongVatThamCoBienModel.setDoTuoi(rs.getString("dotuoi"));
				dongVatThamCoBienModel.setHinhThucSinhSan(rs.getString("hinhthucsinhsan"));
				
				listDongVatThamCoBienModel.add(dongVatThamCoBienModel);
			}
		} catch (Exception e) {
			System.out.println("Can't database excute");
		} finally{
			closeConnect();
		}
		return listDongVatThamCoBienModel;
	}
	
	public ArrayList<DongVatChanNuoiModel> listPointDongVatChanNuoiModel() throws SQLException{
		con = connectDatabaseDAO.connect();
		ArrayList<DongVatChanNuoiModel> listPointDongVatChanNuoi = new ArrayList<DongVatChanNuoiModel>();
		try {
			st = con.createStatement();
			String sql = "SELECT iddongvat_channuoi_vitri, iddongvat_channuoi, ST_X(\"ViTri\")" 
				+ " as longitude, ST_Y(\"ViTri\") as latitude, bankinh, matdo FROM dongvat_channuoi_vitri;";
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				DongVatChanNuoiModel dongVatChanNuoiModel = new DongVatChanNuoiModel();				
				dongVatChanNuoiModel.setIdDVChanNuoiViTri(rs.getString("iddongvat_channuoi_vitri"));
				dongVatChanNuoiModel.setIdDongVatChanNuoi(rs.getString("iddongvat_channuoi"));
				dongVatChanNuoiModel.setViTri_Latitude(rs.getDouble("latitude"));
				dongVatChanNuoiModel.setVitri_Longitude(rs.getDouble("longitude"));
				dongVatChanNuoiModel.setBankinh(rs.getFloat("bankinh"));
				dongVatChanNuoiModel.setMatdo(rs.getString("matdo"));
				
				listPointDongVatChanNuoi.add(dongVatChanNuoiModel);
			}
		} catch (Exception e) {
			System.out.println("Can't database excute");
		} finally{
			closeConnect();
		}
		return listPointDongVatChanNuoi;
	}
	
	public ArrayList<DongVatLamSanNgoaiGoModel> listPointDongVatLamSanNgoaiGoModel() throws SQLException{
		con = connectDatabaseDAO.connect();
		ArrayList<DongVatLamSanNgoaiGoModel> listPointDongVatLamSanNgoaiGo = new ArrayList<DongVatLamSanNgoaiGoModel>();
		try {
			st = con.createStatement();
			String sql = "SELECT iddongvat_lamsanngoaigo_vitri, iddongvat_lamsanngoaigo, ST_X(\"ViTri\")" 
				+ " as longitude, ST_Y(\"ViTri\") as latitude, bankinh, matdo FROM dongvat_lamsanngoaigo_vitri;";
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				DongVatLamSanNgoaiGoModel dongVatLamSanNgoaiGoModel = new DongVatLamSanNgoaiGoModel();				
				dongVatLamSanNgoaiGoModel.setIdDVLamSanNgoaiGoViTri(rs.getString("iddongvat_lamsanngoaigo_vitri"));
				dongVatLamSanNgoaiGoModel.setIdDVLamSanNgoaiGo(rs.getString("iddongvat_lamsanngoaigo"));
				dongVatLamSanNgoaiGoModel.setViTri_Latitude(rs.getDouble("latitude"));
				dongVatLamSanNgoaiGoModel.setVitri_Longitude(rs.getDouble("longitude"));
				dongVatLamSanNgoaiGoModel.setBankinh(rs.getFloat("bankinh"));
				dongVatLamSanNgoaiGoModel.setMatdo(rs.getString("matdo"));
				
				listPointDongVatLamSanNgoaiGo.add(dongVatLamSanNgoaiGoModel);
			}
		} catch (Exception e) {
			System.out.println("Can't database excute");
		} finally{
			closeConnect();
		}
		return listPointDongVatLamSanNgoaiGo;
	}
	
	public ArrayList<DongVatSanHoModel> listPointDongVatSanHoModel() throws SQLException{
		con = connectDatabaseDAO.connect();
		ArrayList<DongVatSanHoModel> listPointDongVatSanHo = new ArrayList<DongVatSanHoModel>();
		try {
			st = con.createStatement();
			String sql = "SELECT iddongvat_sanho_vitri, iddongvat_sanho, ST_X(\"ViTri\")" 
				+ " as longitude, ST_Y(\"ViTri\") as latitude, bankinh, matdo FROM dongvat_sanho_vitri;";
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				DongVatSanHoModel dongVatSanHoModel = new DongVatSanHoModel();				
				dongVatSanHoModel.setIdDongVatSanHo(rs.getString("iddongvat_sanho"));
				dongVatSanHoModel.setIdDVSanHoViTri(rs.getString("iddongvat_sanho_vitri"));
				dongVatSanHoModel.setViTri_Latitude(rs.getDouble("latitude"));
				dongVatSanHoModel.setVitri_Longitude(rs.getDouble("longitude"));
				dongVatSanHoModel.setBankinh(rs.getFloat("bankinh"));
				dongVatSanHoModel.setMatdo(rs.getString("matdo"));
				
				listPointDongVatSanHo.add(dongVatSanHoModel);
			}
		} catch (Exception e) {
			System.out.println("Can't database excute");
		} finally{
			closeConnect();
		}
		return listPointDongVatSanHo;
	}
	
	public ArrayList<DongVatThamCoBienModel> listPointDongVatThamCoBienModel() throws SQLException{
		con = connectDatabaseDAO.connect();
		ArrayList<DongVatThamCoBienModel> listPointDongVatThamCoBien = new ArrayList<DongVatThamCoBienModel>();
		try {
			st = con.createStatement();
			String sql = "SELECT iddongvat_thamcobien_vitri, iddongvat_thamcobien, ST_X(\"ViTri\")" 
				+ " as longitude, ST_Y(\"ViTri\") as latitude, bankinh, matdo FROM dongvat_thamcobien_vitri;";
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				DongVatThamCoBienModel dongVatThamCoBienModel = new DongVatThamCoBienModel();				
				dongVatThamCoBienModel.setIdDongVatThamCoBien(rs.getString("iddongvat_thamcobien_vitri"));
				dongVatThamCoBienModel.setIdDVThamCoBienViTri(rs.getString("iddongvat_thamcobien"));
				dongVatThamCoBienModel.setViTri_Latitude(rs.getDouble("latitude"));
				dongVatThamCoBienModel.setVitri_Longitude(rs.getDouble("longitude"));
				dongVatThamCoBienModel.setBankinh(rs.getFloat("bankinh"));
				dongVatThamCoBienModel.setMatdo(rs.getString("matdo"));
				
				listPointDongVatThamCoBien.add(dongVatThamCoBienModel);
			}
		} catch (Exception e) {
			System.out.println("Can't database excute");
		} finally{
			closeConnect();
		}
		return listPointDongVatThamCoBien;
	}
}
